﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace ECommerce.DAO.SQL.Personas
{
    public static class Persona
    {
        public static ECommerce.Model.Personas.Persona GetPersonaById(Int32 Id)
        {
            ECommerce.Model.Personas.Persona Persona = new ECommerce.Model.Personas.Persona();
            DataTable dt = new DataTable();
            using (SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["Repuestos"].ConnectionString))
            {
                using (SqlCommand sqlComm = new SqlCommand("GetPersonaById"))
                {
                    sqlComm.CommandType = CommandType.StoredProcedure;
                    SqlParameter param = new SqlParameter();
                    param.SqlDbType = SqlDbType.BigInt;
                    param.Value = Id;
                    param.ParameterName = "@Id";
                    sqlComm.Parameters.Add(param);

                    sqlComm.Connection = sqlConn;
                    sqlConn.Open();
                    using (SqlDataReader dr = sqlComm.ExecuteReader())
                    {
                        dt.Load(dr);
                    }
                    foreach (DataRow dr in dt.Rows)
                    {
                        Persona.Load(dr);
                    }
                    sqlConn.Close();
                }
            }
            return Persona;
        }

        public static int SetPersona(ECommerce.Model.Personas.Persona Persona)
        {
            int result = 0;
            using (SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["Repuestos"].ConnectionString))
            {
                using (SqlCommand sqlComm = new SqlCommand("SetPersona"))
                {
                    sqlComm.CommandType = CommandType.StoredProcedure;
                    SqlParameter param = new SqlParameter();
                    param.SqlDbType = SqlDbType.BigInt;
                    param.Value = Persona.Id;
                    param.ParameterName = "@Id";
                    sqlComm.Parameters.Add(param);
                    SqlParameter param0 = new SqlParameter();
                    param0.SqlDbType = SqlDbType.NVarChar;
                    param0.Value = Persona.Apellido;
                    param0.ParameterName = "@Apellido";
                    sqlComm.Parameters.Add(param0); 
                    SqlParameter param1 = new SqlParameter();
                    param1.SqlDbType = SqlDbType.NVarChar;
                    param1.Value = Persona.Nombre;
                    param1.ParameterName = "@Nombre";
                    sqlComm.Parameters.Add(param1); 
                    SqlParameter param2 = new SqlParameter();
                    param2.SqlDbType = SqlDbType.NVarChar;
                    param2.Value = Persona.IdGov;
                    param2.ParameterName = "@IdGov";
                    sqlComm.Parameters.Add(param2); 

                    sqlComm.Connection = sqlConn;
                    sqlConn.Open();
                    result = (int)sqlComm.ExecuteScalar();
                    sqlConn.Close();
                }
            }
            return result;
        }

        public static void AddEmailToPersona(ECommerce.Model.Comunicaciones.Email Email, ECommerce.Model.Personas.Persona Persona)
        {
            using (SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["Repuestos"].ConnectionString))
            {
                using (SqlCommand sqlComm = new SqlCommand("AddEmailToPersona"))
                {
                    sqlComm.CommandType = CommandType.StoredProcedure;
                    SqlParameter param = new SqlParameter();
                    param.SqlDbType = SqlDbType.BigInt;
                    param.Value = Persona.Id;
                    param.ParameterName = "@IdPersona";
                    sqlComm.Parameters.Add(param);
                    SqlParameter param0 = new SqlParameter();
                    param0.SqlDbType = SqlDbType.BigInt;
                    param0.Value = Email.Id;
                    param0.ParameterName = "@IdEmail";
                    sqlComm.Parameters.Add(param0);
                    sqlComm.Connection = sqlConn;
                    sqlConn.Open();
                    sqlComm.ExecuteScalar();
                    sqlConn.Close();
                }
            }
        }

        public static void AddTeléfonoToPersona(ECommerce.Model.Comunicaciones.Teléfono Teléfono, ECommerce.Model.Personas.Persona Persona)
        {
            using (SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["Repuestos"].ConnectionString))
            {
                using (SqlCommand sqlComm = new SqlCommand("AddTeléfonoToPersona"))
                {
                    sqlComm.CommandType = CommandType.StoredProcedure;
                    SqlParameter param = new SqlParameter();
                    param.SqlDbType = SqlDbType.BigInt;
                    param.Value = Persona.Id;
                    param.ParameterName = "@IdPersona";
                    sqlComm.Parameters.Add(param);
                    SqlParameter param0 = new SqlParameter();
                    param0.SqlDbType = SqlDbType.BigInt;
                    param0.Value = Teléfono.Id;
                    param0.ParameterName = "@IdTeléfono";
                    sqlComm.Parameters.Add(param0);
                    sqlComm.Connection = sqlConn;
                    sqlConn.Open();
                    sqlComm.ExecuteScalar();
                    sqlConn.Close();
                }
            }
        }

    }
}
